package com.gjm.tool.utils;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import com.alibaba.fastjson.JSON;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.apache.commons.lang3.StringUtils;

import java.io.*;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

public class FileCheck {

    private static String canBrushTheNumberfileSqlwithcertNo = "update  ctc_cc_cust_info_t set cert_no= '%s' where  business_code='%s';";
    private static String canBrushTheNumberfileSqlWithBuidDate = "update  ctc_cc_cust_info_t set build_date= '%s' where  business_code='%s';";
    private static String canBrushTheNumberfileSqlWithLegalPerson = "update  ctc_cc_cust_info_t set legal_person= '%s' where  business_code='%s';";
    private static String canBrushTheNumberfileSqlwithregisterCost = "update  ctc_cc_cust_info_t set register_cost= '%s' where  business_code='%s';";

    public static void main(String[] args) throws Exception {

        Set<String> problemSet = mainData();

        /**
         * 1.查询可关闭的数据
         *   1.1 查询主数据所有的整改范围内数据
         *   1.2 通过客户客商编码匹配我侧数据
         *   1.3 整合计费+订单所有存在的业务数据，存储客户id
         *   1.4 将1.2结果去掉1.3结果=可关闭数据
         *   1.5 检查业务代码，客户关闭后，是否存在问题
         *   1.6 生产不可关闭集合
         */
        String canBrushTheNumberfile = "C:\\Users\\gjm\\Desktop\\司库\\统一社会信用代码可以刷数的数据.txt";
        String unCanBrushTheNumberfile = "C:\\Users\\gjm\\Desktop\\司库\\统一社会信用代码不可以刷数的数据.txt";
        String notExist = "C:\\Users\\gjm\\Desktop\\司库\\统一社会信用代码不存在的数据.txt";
        FileUtil.clearInfoForFile(canBrushTheNumberfile);
        FileUtil.clearInfoForFile(unCanBrushTheNumberfile);
        FileUtil.clearInfoForFile(notExist);
        List<List<Object>> mysql = mysql();
        //我侧数据，以客户编码为主维度
        Map<String, List<List<Object>>> custBusinessCodeMap = new HashMap<>();
        //我侧数据，以certNo为主维度
        Map<String, List<List<Object>>> mysqlCertNoMap = new HashMap<>();

        for (List<Object> row : mysql) {
            String businessCode = String.valueOf(row.get(0));
            List<List<Object>> list = custBusinessCodeMap.get(businessCode);
            if (!custBusinessCodeMap.containsKey(businessCode)) {
                list = new ArrayList<>();
            }
            list.add(row);
            custBusinessCodeMap.put(businessCode, list);

            String certNo = String.valueOf(row.get(1));
            if (StringUtils.isNotBlank(certNo)) {
                List<List<Object>> certNoList = mysqlCertNoMap.get(certNo);
                if (!mysqlCertNoMap.containsKey(certNo)) {
                    certNoList = new ArrayList<>();
                }
                certNoList.add(row);
                mysqlCertNoMap.put(certNo, certNoList);
            }
        }

        //主数据数据
        List<List<Object>> mainData = mainDataWithSheet1();
        List<List<Object>> mainDataWithCertNoNotEqMysql = new ArrayList<>();

        //1. 统一社会信用编码--已改数脚本
        Map<String, String> certNoMap = sqlReadOneWithCertNo();

        for (List<Object> mainDatum : mainData) {
            String mainBusinessCode = String.valueOf(mainDatum.get(1));
            if (certNoMap.containsKey(mainBusinessCode)){
                continue;
            }
            String mainCertNo = String.valueOf(mainDatum.get(3));
            String certNo = String.valueOf(mainDatum.get(4));
            if (!mainCertNo.equals(certNo)){
                mainDataWithCertNoNotEqMysql.add(mainDatum);
            }
        }


        for (List<Object> row : mainDataWithCertNoNotEqMysql) {
            String mainBusinessCode = String.valueOf(row.get(1));
            List<List<Object>> list = custBusinessCodeMap.get(mainBusinessCode);
            if (CollectionUtil.isEmpty(list)) {
                //客户管理侧此businessCode不存在 没办法刷数
                FileUtil.appendInfoToFile(notExist, String.valueOf(row.get(3)) + "+" + row.get(2) + "+" + String.valueOf(row.get(1)));
            } else {
                List<List<Object>> mysqlCertNoList = mysqlCertNoMap.get(String.valueOf(row.get(3)));
                //我侧不存在certNo 可以刷数
                if (CollectionUtil.isEmpty(mysqlCertNoList)) {
                    fileExportWithSqlShell(problemSet,row,canBrushTheNumberfile, String.format(canBrushTheNumberfileSqlwithcertNo, String.valueOf(row.get(3)), String.valueOf(row.get(1))));
                } else {
                    int count = 0;
                    String errMsg = "已被其它客商使用，不可以刷数,主数据custName：%s,主数据businessCode：%s,主数据certNo:%s; 被占用businessCode：%s,被占用certNo:%s,被占用custName:%s；";
                    for (List<Object> objects : mysqlCertNoList) {
                        if (!Objects.equals(row.get(1), objects.get(0)) && Objects.equals(row.get(3), objects.get(1))) {
                            errMsg = String.format(errMsg,row.get(2),row.get(1),row.get(3),objects.get(0),objects.get(1),objects.get(8));
                            count++;
                        }
                    }
                    if (count == 0) {
                        // 未被其它客商使用，可以刷数
                        fileExportWithSqlShell(problemSet,row,canBrushTheNumberfile, String.format(canBrushTheNumberfileSqlwithcertNo, String.valueOf(row.get(3)), String.valueOf(row.get(1))));
                    } else {
                        // 已被其它客商使用，不可以刷数 -- 统一社会信用代码不可以刷数的数据
                        FileUtil.appendInfoToFile(unCanBrushTheNumberfile, errMsg);
                    }
                }
            }
        }






        //所有的唯一校验条件 统一社会信用编码+客户名称+客商名称
        List<String> custLineList = new ArrayList<>();




        List<List<Object>> mainDataWithCertNoEqMysql = new ArrayList<>();  //主数据的CertNo=我侧的CertNo
          //主数据的CertNo ！=我侧的CertNo  且主数据certNo不为空 需要处理的数据

        List<List<Object>> mysqlNotExist = new ArrayList<>(); //我侧数据不存在
        List<List<Object>> canBrushTheNumber = new ArrayList<>(); //可以刷数的数据
        List<List<Object>> unCanBrushTheNumber = new ArrayList<>(); //不可以刷数的数据

        Map<String, List<Object>> mainBusinessCodeMap = new HashMap<>();

//        String canBrushTheNumberfileWithBuildDate = "C:\\Users\\gjm\\Desktop\\司库\\成立日期可以刷数的数据.txt";
//        String notExistWithBuildDate = "C:\\Users\\gjm\\Desktop\\司库\\成立日期不存在的数据.txt";
//        FileUtil.clearInfoForFile(canBrushTheNumberfileWithBuildDate);
//        FileUtil.clearInfoForFile(notExistWithBuildDate);
//        List<List<Object>> mainData2 = mainDataWithSheet2();
//        for (List<Object> row : mainData2) {
//            if (StringUtils.isBlank(String.valueOf(row.get(3)))) {
//                continue;
//            }
//            String mainBusinessCode = String.valueOf(row.get(1));
//            mainBusinessCodeMap.put(mainBusinessCode, row);
//            List<List<Object>> list = custBusinessCodeMap.get(mainBusinessCode);
//            if (CollectionUtil.isEmpty(list)) {
//                //客户管理侧数据不存在 没办法刷数
//                mysqlNotExist.add(row);
//                FileUtil.appendInfoToFile(notExistWithBuildDate, String.valueOf(row.get(3)) + "+" + row.get(2) + "+" + String.valueOf(row.get(1)));
//            } else {
//                FileUtil.appendInfoToFile(canBrushTheNumberfileWithBuildDate, String.format(canBrushTheNumberfileSqlWithBuidDate, String.valueOf(row.get(3)), String.valueOf(row.get(1))));
//            }
//        }
//
//        String canBrushTheNumberfileWithLegalPerson = "C:\\Users\\gjm\\Desktop\\司库\\法定代表人可以刷数的数据.txt";
//        String notExistWithLegalPerson = "C:\\Users\\gjm\\Desktop\\司库\\法定代表人不存在的数据.txt";
//        FileUtil.clearInfoForFile(canBrushTheNumberfileWithLegalPerson);
//        FileUtil.clearInfoForFile(notExistWithLegalPerson);
//        List<List<Object>> mainData3 = mainDataWithSheet3();
//        for (List<Object> row : mainData3) {
//            if (StringUtils.isBlank(String.valueOf(row.get(3)))) {
//                continue;
//            }
//            String mainBusinessCode = String.valueOf(row.get(1));
//            mainBusinessCodeMap.put(mainBusinessCode, row);
//            List<List<Object>> list = custBusinessCodeMap.get(mainBusinessCode);
//            if (CollectionUtil.isEmpty(list)) {
//                //客户管理侧数据不存在 没办法刷数
//                mysqlNotExist.add(row);
//                FileUtil.appendInfoToFile(notExistWithLegalPerson, String.valueOf(row.get(3)) + "+" + row.get(2) + "+" + String.valueOf(row.get(1)));
//            } else {
//                FileUtil.appendInfoToFile(canBrushTheNumberfileWithLegalPerson, String.format(canBrushTheNumberfileSqlWithLegalPerson, String.valueOf(row.get(3)), String.valueOf(row.get(1))));
//            }
//        }
//
//        String canBrushTheNumberfileWithRegisterCost = "C:\\Users\\gjm\\Desktop\\司库\\注册资本可以刷数的数据.txt";
//        String notExistWithRegisterCost = "C:\\Users\\gjm\\Desktop\\司库\\注册资本不存在的数据.txt";
//        FileUtil.clearInfoForFile(canBrushTheNumberfileWithRegisterCost);
//        FileUtil.clearInfoForFile(notExistWithRegisterCost);
//        List<List<Object>> mainData4 = mainDataWithSheet4();
//        for (List<Object> row : mainData4) {
//            if (StringUtils.isBlank(String.valueOf(row.get(3)))) {
//                continue;
//            }
//            String mainBusinessCode = String.valueOf(row.get(1));
//            mainBusinessCodeMap.put(mainBusinessCode, row);
//            List<List<Object>> list = custBusinessCodeMap.get(mainBusinessCode);
//            if (CollectionUtil.isEmpty(list)) {
//                //客户管理侧数据不存在 没办法刷数
//                mysqlNotExist.add(row);
//                FileUtil.appendInfoToFile(notExistWithRegisterCost, String.valueOf(row.get(3)) + "+" + row.get(2) + "+" + String.valueOf(row.get(1)));
//            } else {
//                FileUtil.appendInfoToFile(canBrushTheNumberfileWithRegisterCost, String.format(canBrushTheNumberfileSqlwithregisterCost, String.valueOf(row.get(3)), String.valueOf(row.get(1))));
//            }
//        }
        System.out.println("程序结束");

    }

    public static List<List<Object>> mysql() {
        String filePath = "C:\\Users\\gjm\\Desktop\\司库\\四个字段的全量数据.xlsx";
        ExcelReader reader = ExcelUtil.getReader(filePath);
        // 读取指定sheet页面，0表示第一个sheet
        List<List<Object>> sheetList = reader.read(0);
        // 关闭读取器，释放资源
        reader.close();
        return sheetList.subList(1, sheetList.size());
    }


    public static List<List<Object>> mainDataWithSheet1() {
        String filePath = "C:\\Users\\gjm\\Desktop\\司库\\客户与客商信息不一致数据清单_20240922.xlsx";
        ExcelReader reader = ExcelUtil.getReader(filePath, 1);
        // 读取指定sheet页面，0表示第一个sheet
        List<List<Object>> sheetList = reader.read(0);
        // 关闭读取器，释放资源
        reader.close();
        return sheetList.subList(2, sheetList.size());
    }



    /**
     *
     * 读取改数脚本--统一社会行用编码
     * update  ctc_cc_cust_info_t set cert_no= '91520502MA6DJ08T1L' where  business_code='020100414747';
     * (?<=cert_no= ')([^']+)
     * (?<=business_code=')(.*?)(?=')
     */
    public static Map<String, String> sqlReadOneWithCertNo() throws Exception {
        String filePath = "C:\\Users\\gjm\\Desktop\\司库\\客商和客户可以刷数的脚本\\客户管理可以刷数的脚本V1.2\\统一社会信用代码可以刷数的数据.txt";
        Map map = new HashMap();
        List<String> list = FileUtil.readInfoFromFile(filePath);
        for (String string : list) {
            List<String> certNoList = find("(?<=cert_no= ')([^']+)", string);
            List<String> businessCodeList = find("(?<=business_code=')(.*?)(?=')", string);
            if (CollectionUtil.isEmpty(certNoList) || CollectionUtil.isEmpty(businessCodeList)) {
                continue;
            }
            String certNo = certNoList.get(0);
            String businessCode = businessCodeList.get(0);
            map.put(businessCode, certNo);
        }
        return map;
    }

    /**
     * @param regex 正则表达式字符串
     * @param str   要匹配的字符串
     * @return;
     */
    public static List<String> find(String regex, String str) {
        List<String> list = new ArrayList<>();
        if (StrUtil.isBlank(regex) || StrUtil.isBlank(str)) {
            return list;
        }
        Pattern pattern = Pattern.compile(regex);
        Matcher matcher = pattern.matcher(str);
        while (matcher.find()) {
            list.add(matcher.group());
        }
        return list;
    }


    public static List<List<Object>> mainDataWithSheet2() {
        String filePath = "C:\\Users\\gjm\\Desktop\\司库\\客户与客商信息不一致数据清单_20240922.xlsx";
        ExcelReader reader = ExcelUtil.getReader(filePath, 2);
        // 读取指定sheet页面，0表示第一个sheet
        List<List<Object>> sheetList = reader.read(0);
        // 关闭读取器，释放资源
        reader.close();
        return sheetList.subList(2, sheetList.size());
    }

    public static List<List<Object>> mainDataWithSheet3() {
        String filePath = "C:\\Users\\gjm\\Desktop\\司库\\客户与客商信息不一致数据清单_20240922.xlsx";
        ExcelReader reader = ExcelUtil.getReader(filePath, 3);
        // 读取指定sheet页面，0表示第一个sheet
        List<List<Object>> sheetList = reader.read(0);
        // 关闭读取器，释放资源
        reader.close();
        return sheetList.subList(2, sheetList.size());
    }

    public static List<List<Object>> mainDataWithSheet4() {
        String filePath = "C:\\Users\\gjm\\Desktop\\司库\\客户与客商信息不一致数据清单_20240922.xlsx";
        ExcelReader reader = ExcelUtil.getReader(filePath, 4);
        // 读取指定sheet页面，0表示第一个sheet
        List<List<Object>> sheetList = reader.read(0);
        // 关闭读取器，释放资源
        reader.close();
        return sheetList.subList(2, sheetList.size());
    }

    public static List<String> getOrderCustId() {
        String filePath = "C:\\Users\\gjm\\Desktop\\司库\\不合规数据提取-整理数据可反馈业支--订单反馈.xlsx";
        ExcelReader reader = ExcelUtil.getReader(filePath);
        // 读取指定sheet页面，0表示第一个sheet
        List<List<Object>> sheetList = reader.read(0);
        // 关闭读取器，释放资源
        reader.close();
        List<String> custIdList = sheetList.subList(2, sheetList.size()).stream().filter(i -> "是".equals(String.valueOf(i.get(26)))).map(i -> String.valueOf(i.get(0))).collect(Collectors.toList());
        return custIdList;
    }

    public static List<String> getBillCustId() {
        String filePath = "C:\\Users\\gjm\\Desktop\\司库\\不合规数据提取-整理数据可反馈业支--计费反馈.xlsx";
        ExcelReader reader = ExcelUtil.getReader(filePath);
        // 读取指定sheet页面，0表示第一个sheet
        List<List<Object>> sheetList = reader.read(0);
        // 关闭读取器，释放资源
        reader.close();
        List<String> custIdList = sheetList.subList(2, sheetList.size()).stream().filter(i -> "是".equals(String.valueOf(i.get(0)))).map(i -> String.valueOf(i.get(1))).collect(Collectors.toList());
        return custIdList;
    }


    public static Set<String> mainData( ) {
        HashSet<String>  set= new HashSet<>();
        String filePath = "C:\\Users\\gjm\\Desktop\\司库\\客户与客商信息不一致数据清单_20240922.xlsx";
        ExcelReader reader = ExcelUtil.getReader(filePath, 1);
        // 读取指定sheet页面，0表示第一个sheet
        List<List<Object>> sheetList = reader.read(0);
        // 关闭读取器，释放资源
        reader.close();
        List<List<Object>> list = sheetList.subList(2, sheetList.size());
        List<MainVO> mainList = new ArrayList<>();
        for (List<Object> objects : list) {
            mainList.add(new MainVO(String.valueOf(objects.get(1)), String.valueOf(objects.get(2)), String.valueOf(objects.get(3))));
        }
        Map<String, List<MainVO>> map = mainList.stream().collect(Collectors.groupingBy(MainVO::getCustCode));
        for (Map.Entry<String, List<MainVO>> entry : map.entrySet()) {
            if (CollectionUtil.isEmpty(entry.getValue()) || entry.getValue().size() <= 1) {
                continue;
            }
            Set<String> nameSet = entry.getValue().stream().map(i -> i.getCustName()).collect(Collectors.toSet());
            Set<String> certNoSet = entry.getValue().stream().map(i -> i.getCertNo()).collect(Collectors.toSet());

            if (nameSet.size() > 1 || certNoSet.size() > 1) {
                for (MainVO mainVO : entry.getValue()) {
                    set.add(mainVO.getCustCode());
                    System.out.println(JSON.toJSONString(mainVO));
                }

            }
        }
        return set;
    }

    /**
     *
     * @param problemSet 是businessCode
     * @param row
     * @param fileName
     * @param sqlShell
     */
    public static void fileExportWithSqlShell(Set<String> problemSet,List<Object> row, String fileName, String sqlShell) {
        if (problemSet.contains(String.valueOf(row.get(1)))) {
            System.out.println("fileExportWithSqlShell:"+row.get(1));
            return;
        }
        FileUtil.appendInfoToFile(fileName, String.format(sqlShell, String.valueOf(row.get(3)), String.valueOf(row.get(1))));
    }

    @AllArgsConstructor
    @NoArgsConstructor
    @Data
    static class MainVO {
        private String custCode;
        private String custName;
        private String certNo;
    }


}



